/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;

/**
 *
 * @author ivanlin
 */
public class ExportOTWCoordinator
{
  private Connection conn;
  private PreparedStatement pst;
  private ResultSet rs;

  public static void main(String[] args)
  {
    new ExportOTWCoordinator().export();
  }

  public void export()
  {
    List<ExportItem> coordinatorItems = new ArrayList<ExportItem>(1000);
    List<CompanyItem> companyItems = new ArrayList<CompanyItem>(50);
    List<DistrictItem> districtItems = new ArrayList<DistrictItem>(50);
    List<RegionItem> regionItems = new ArrayList<RegionItem>(50);
    List<TougueItem> tougueItems = new ArrayList<TougueItem>(50);
    Map<Integer, CompanyItem> allCompanys = new HashMap<Integer, CompanyItem>();
    
    try
    {
      conn = JDBCUtil.getConnection();

      // Coordinator
      String sql = "select o.id, o.teamno, o.teamCategory, o.company_id, c.id, "
              + "c.address, c.building, c.chname, c.chsurename, c.correspondencetype, "
              + "c.defaulted, c.disabled, c.email, c.enfirstname, c.enlastname, c.enmiddlename, c.enothername, "
              + "c.estate, c.job, c.priphoneno, c.priphonecountrycode, "
              + "c.room, c.secphonecountrycode, c.secphoneno, c.status, c.street, c.title, c.type, "
              + "c.company_id, c.district_id, c.region_id, c.tongue_id, c.companycoordinatorid"
              + " from otwteamcoordinator c, otwteam o where o.id in "
              + "(select distinct teams_id from otwteam_otwteamcoordinator where coordinators_id = c.id) "
              + "order by o.teamno";
      pst = conn.prepareStatement(sql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        coordinatorItems.add(this.transferCoordinatorData(rs));
      }
      System.out.println("Coordinator Count : " + coordinatorItems.size());

      //Find All Company
      String allCompanySql = "select c.id, c.nameEn, c.nameCh from Company c";
      pst = conn.prepareStatement(allCompanySql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        CompanyItem item = this.transferParameterData(rs, new CompanyItem());
        allCompanys.put(item.getId(), item);
      }

      // Coordinator Company
      String companySql = "select distinct c.id, c.nameEn, c.nameCh from Company c where c.id in (select o.company_id from OTWTeamCoordinator o)";
      pst = conn.prepareStatement(companySql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        companyItems.add(this.transferParameterData(rs, new CompanyItem()));
      }
      System.out.println("Conpany Count : " + companyItems.size());

      // District
      String districtSql = "select distinct c.id, c.nameEn, c.nameCh from District c where c.id in (select o.district_id from OTWTeamCoordinator o)";
      pst = conn.prepareStatement(districtSql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        districtItems.add(this.transferParameterData(rs, new DistrictItem()));
      }
      System.out.println("District Count : " + districtItems.size());

      // Region
      String regionSql = "select distinct c.id, c.nameEn, c.nameCh from Region c where c.id in (select o.region_id from OTWTeamCoordinator o)";
      pst = conn.prepareStatement(regionSql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        regionItems.add(this.transferParameterData(rs, new RegionItem()));
      }
      System.out.println("Region Count : " + companyItems.size());

      // Tougue
      String tougueSql = "select distinct c.id, c.nameEn, c.nameCh from Tongue c where c.id in (select o.tongue_id from OTWTeamCoordinator o)";
      pst = conn.prepareStatement(tougueSql);
      rs = pst.executeQuery();
      while (rs.next())
      {
        tougueItems.add(this.transferParameterData(rs, new TougueItem()));
      }
      System.out.println("Tougue Count : " + companyItems.size());

    }
    catch (SQLException ex)
    {
      Logger.getLogger(ExportOTWCoordinator.class.getName()).log(Level.SEVERE, null, ex);
    }
    finally
    {
      JDBCUtil.close(conn, pst, rs);
    }
    
    try
    {
      ExportHandler.get().doExport(coordinatorItems, allCompanys, companyItems, districtItems, regionItems, tougueItems);
    }
    catch (IOException ex)
    {
      Logger.getLogger(ExportOTWCoordinator.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  private ExportItem transferCoordinatorData(ResultSet rs) throws SQLException
  {
    ExportItem item = new ExportItem();
    item.setTeamId(rs.getLong(1));
    item.setTeamNo(rs.getString(2));
    item.setTeamCategory(rs.getString(3));
    item.setTeamCompanyID(rs.getInt(4));
    item.setId(rs.getLong(5));
    item.setAddress(rs.getString(6));
    item.setBuilding(rs.getString(7));
    item.setChName(rs.getString(8));
    item.setChSureName(rs.getString(9));
    item.setCorrespondenceType(rs.getString(10));
    item.setDefaulted(rs.getBoolean(11));
    item.setDisabled(rs.getBoolean(12));
    item.setEmail(rs.getString(13));
    item.setEnFirstName(rs.getString(14));
    item.setEnLastName(rs.getString(15));
    item.setEnMiddleName(rs.getString(16));
    item.setEnOtherName(rs.getString(17));
    item.setEstate(rs.getString(18));
    item.setJob(rs.getString(19));
    item.setPriPhoneNo(rs.getString(20));
    item.setPriPhoneCountryCode(rs.getString(21));
    item.setRoom(rs.getString(22));
    item.setSecPhoneCountryCode(rs.getString(23));
    item.setSecPhoneNo(rs.getString(24));
    item.setStatus(rs.getString(25));
    item.setStreet(rs.getString(26));
    item.setTitle(rs.getString(27));
    item.setType(rs.getString(28));
    item.setCompanyId(rs.getInt(29));
    item.setDistrict(rs.getInt(30));
    item.setRegion(rs.getInt(31));
    item.setTongueId(rs.getInt(32));
    item.setCompanyCoordinatorId(rs.getLong(33));
    
    return item;
  }

  private <T extends ParameterItem> T transferParameterData(ResultSet rs, T item) throws SQLException
  {
    int column = 1;
    item.setId(rs.getInt(column++));
    item.setNameEn(rs.getString(column++));
    item.setNameCH(rs.getString(column++));

    return item;
  }
}
